/**
 * 文件名：ExcelUtil.java
 * 版本信息：1.0
 * 日期：2015年10月23日-下午2:45:55
 */
package meterMonitor.util.excel;

import jodd.bean.BeanUtil;
import jodd.util.StringUtil;
import meterMonitor.mapper.MeterMapper;
import meterMonitor.model.Area;
import meterMonitor.util.excel.domain.ExcelColumn;
import meterMonitor.util.excel.domain.ExcelHead;
import meterMonitor.util.excel.domain.Hxls2List;
import meterMonitor.util.excel.domain.Xxlsx2List;
import meterMonitor.vo.DIYRepExport;
import meterMonitor.vo.MeterInfoShort;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * ExcelUtil
 *
 * @author qian.xu
 * @date 2015年10月23日 下午2:45:55
 */
public class ExcelUtil {
    public static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    private static ExcelUtil helper = null;

    private static final int defaultColumnWidth = 3500;

    private DataFormat format;

    /**
     * Excel样式
     */
    private CellStyle headerStyle, commonStyle;

    /**
     * Excel日期格式
     */
    private CellStyle defaultDateCellStyle;

    /**
     * 自定义的日期格式（索引，对应的格式）
     */
    private Map<Integer, CellStyle> mapDateCellStyle;

    private ExcelUtil() {
    }

    public static synchronized ExcelUtil getInstanse() {
        if (helper == null) {
            helper = new ExcelUtil();
        }
        return helper;
    }

    /**
     * 将Excel文件导入到list对象
     *
     * @param head 文件头信息
     * @param is   导入的数据流
     * @param cls  保存当前数据的对象
     * @return List
     */
    public List importExcelToObjectList(ExcelHead head, InputStream is, Class cls) {
        if (null == head || null == is || null == cls) {
            return null;
        }
        ArrayList<Object> contents = new ArrayList<Object>();
        try {
            List<ExcelColumn> excelColumns = head.getColumns();
            Map<String, Map> columnsConvertMap = head.getColumnsConvertMap();//需要转换的列
            if (!is.markSupported()) {
                is = new PushbackInputStream(is);
            }
            if (POIFSFileSystem.hasPOIFSHeader(is)) {// EXCEL2003使用的是微软的文件系统
                Hxls2List hxls2List = new Hxls2List(is, convertExcelHeadToMap(excelColumns), columnsConvertMap, cls,
                        contents);
                hxls2List.process();
            } else if (POIXMLDocument.hasOOXMLHeader(is)) {//EXCEL2007使用的是OOM文件格式
                Xxlsx2List xxlsx2List = new Xxlsx2List(convertExcelHeadToMap(excelColumns), columnsConvertMap, contents,
                        cls);
                xxlsx2List.process(is);
            } else {
                throw new IOException("不能解析的Excel版本");
            }
        } catch (FileNotFoundException ex) {
            ex.printStackTrace();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return contents;
    }

    /**
     * 将Excel文件导入到list对象
     *
     * @param head 文件头信息
     * @param file 导入的数据源
     * @param cls  保存当前数据的对象
     * @return List
     */
    public List importExcelToObjectList(ExcelHead head, File file, Class cls) {
        if (null == head || null == file || null == cls || !file.exists()) {
            return null;
        }
        InputStream is;
        ArrayList<Object> contents = new ArrayList<Object>();
        try {
            is = new BufferedInputStream(new FileInputStream(file));
            List<ExcelColumn> excelColumns = head.getColumns();
            Map<String, Map> columnsConvertMap = head.getColumnsConvertMap();//需要转换的列
            if (!is.markSupported()) {
                is = new PushbackInputStream(is);
            }
            if (POIFSFileSystem.hasPOIFSHeader(is)) {// EXCEL2003使用的是微软的文件系统
                Hxls2List hxls2List = new Hxls2List(file, convertExcelHeadToMap(excelColumns), columnsConvertMap, cls,
                        contents);
                hxls2List.process();
            } else if (POIXMLDocument.hasOOXMLHeader(is)) {//EXCEL2007使用的是OOM文件格式
                Xxlsx2List xxlsx2List = new Xxlsx2List(convertExcelHeadToMap(excelColumns), columnsConvertMap, contents,
                        cls);
                xxlsx2List.process(is);
            } else {
                throw new IOException("不能解析的Excel版本");
            }
        } catch (FileNotFoundException ex) {
            ex.printStackTrace();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return contents;
    }

    /**
     * 将数据导入Excel，返回文件流
     * exportExcelInputStream(这里用一句话描述这个方法的作用)
     *
     * @param head
     * @param fileName
     * @param dataList
     * @return InputStream
     * @throws
     */
    public InputStream exportExcelInputStream(ExcelHead head, String fileName, List<?> dataList) {
        // 读取导出excel模板
        InputStream is = null;
        SXSSFWorkbook wb = new SXSSFWorkbook(1000);//SXSSFWorkbook用来处理大数据量以及超大数据量的导出，内存里一次只留 多少行
        // 生成导出数据
        buildExcelData(wb, head, fileName, dataList, false);
        // 导出到文件中
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        try {
            wb.write(out);
            out.flush();
            byte[] xlsxData = out.toByteArray();
            is = new ByteArrayInputStream(xlsxData, 0, xlsxData.length);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return is;
    }

    /**
     * exportExcel(导出excel)
     *
     * @param head
     * @param fileName
     * @param dataList
     * @return void
     * @throws
     */
    public void exportExcel(ExcelHead head, String fileName, List<?> dataList, HttpServletResponse response) {
        try {
            response.setHeader("content-disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
        } catch (UnsupportedEncodingException e) {
            logger.error(e.getMessage(), e);
        }
        SXSSFWorkbook wb = new SXSSFWorkbook(1000);//SXSSFWorkbook用来处理大数据量以及超大数据量的导出，内存里一次只留 多少行
        // 生成导出数据
        buildExcelData(wb, head, fileName, dataList, false);
        // 导出到文件中
        OutputStream out = null;
        try {
            out = response.getOutputStream();
            wb.write(out);
            out.flush();
        } catch (IOException e) {
            logger.error(e.getMessage(), e);
        } finally {
            try {
                if (out != null) {
                    out.close();
                }
            } catch (IOException e) {
                logger.error(e.getMessage(), e);
            }
        }
    }

    public void exportExcelWithMergeHeaderRow(ExcelHead head, String fileName, List<?> dataList, HttpServletResponse response) {
        try {
            response.setHeader("content-disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
        } catch (UnsupportedEncodingException e) {
            logger.error(e.getMessage(), e);
        }
        // 读取导出excel模板
        SXSSFWorkbook wb = new SXSSFWorkbook(1000);//SXSSFWorkbook用来处理大数据量以及超大数据量的导出，内存里一次只留 多少行
        // 生成导出数据
        buildExcelData(wb, head, fileName, dataList, true);
        // 导出到文件中
        OutputStream out = null;
        try {
            out = response.getOutputStream();
            wb.write(out);
            out.flush();
        } catch (IOException e) {
            logger.error(e.getMessage(), e);
        } finally {
            try {
                if (out != null) {
                    out.close();
                }
            } catch (IOException e) {
                logger.error(e.getMessage(), e);
            }
        }
    }

    /**
     * 将报表结构转换成Map
     *
     * @param excelColumns
     * @return void
     * 2012-4-18 下午01:31:12
     * @auther <a href="mailto:hubo@feinno.com">hubo</a>
     */
    private Map<Integer, ExcelColumn> convertExcelHeadToMap(List<ExcelColumn> excelColumns) {
        Map<Integer, ExcelColumn> excelHeadMap = new HashMap<Integer, ExcelColumn>();
        for (ExcelColumn excelColumn : excelColumns) {
            if (StringUtil.isEmpty(excelColumn.getFieldName())) {
                continue;
            } else {
                excelHeadMap.put(excelColumn.getIndex(), excelColumn);
            }
        }
        return excelHeadMap;
    }

    /**
     * 生成导出至Excel文件的数据
     *
     * @param wb                       工作表
     * @param head                     excel表头
     * @param fileName                 excel表文件名
     * @param mergeSameCellOfHeaderRow 是否自动合并相同值的表头
     * @param dataList                 导入excel报表的数据来源
     * @return void
     * 2012-4-19 上午09:36:37
     */
    private void buildExcelData(SXSSFWorkbook wb, ExcelHead head, String fileName, List<?> dataList, boolean mergeSameCellOfHeaderRow) {
        Sheet sheet = wb.createSheet(fileName);
        List<ExcelColumn> excelColumns = head.getColumns();
        Map<String, Map> excelHeadConvertMap = head.getColumnsConvertMap();
        // 从第几行开始插入
        int startRow = 0;
        int startRow2 = 0;
        initExcelStyle(wb, excelColumns);
        //写表头
        Row row = null;
        int rowCount = head.getRowCount();
        Cell cell = null;
        ExcelColumn excelColumn;
        for (int i = 0; i < rowCount; i++) {
            row = sheet.createRow(startRow++);
            for (int j = 0; j < excelColumns.size(); j++) {
                excelColumn = excelColumns.get(j);
                cell = row.createCell(j);
                cell.setCellValue(excelColumn.getFieldDispName());
                cell.setCellStyle(headerStyle);
                sheet.setColumnWidth(j, (excelColumn.getWidth() <= 0 ? defaultColumnWidth : excelColumn.getWidth()));
            }
        }
        //
        if (mergeSameCellOfHeaderRow) {
            //合并单元格
            List<CellRangeAddress> listMerge = new LinkedList<>();

            for (int i = 0; i < rowCount; i++) {
                int rowIndex = startRow2 + i;
                int startIndex = -1, endIndex = -1;
                for (int j = 0; j < excelColumns.size() - 1; j++) {
                    if (excelColumns.get(j).getFieldDispName().equals(excelColumns.get(j + 1).getFieldDispName())) {
                        //如果是最后一个元素
                        if (j == (excelColumns.size() - 1 - 1)) {
                            //如果当前有需合并单元格
                            if (startIndex != -1) {
                                //则直接合并到最后一个格子
                                endIndex = j + 1;
                                listMerge.add(new CellRangeAddress(rowIndex, rowIndex, startIndex, endIndex));
                            }
                        }

                        if (startIndex == -1) {
                            startIndex = j;
                        }
                    } else {
                        if (startIndex != -1) {
                            endIndex = j;
                            listMerge.add(new CellRangeAddress(rowIndex, rowIndex, startIndex, endIndex));

                            startIndex = -1;
                            endIndex = -1;
                        }
                    }
                }
            }
            //
            for (CellRangeAddress a : listMerge) {
                sheet.addMergedRegion(a);
            }
        }
        //写数据
        Cell upCell;
        for (Object obj : dataList) {
            row = sheet.createRow(startRow++);
            for (int j = 0; j < excelColumns.size(); j++) {
                cell = row.createCell(j);
                cell.setCellStyle(commonStyle);
                excelColumn = excelColumns.get(j);
                String fieldName = excelColumn.getFieldName();
                if (fieldName != null) {
                    upCell = sheet.getRow(startRow - 2).getCell(j);
                    Object valueObject = null;
                    try {
                        valueObject = BeanUtil.getProperty(obj, fieldName);
                    } catch (Exception e) {//防止 fieldName 不存在情况
                        // TODO: handle exception
                    }
                    // 如果存在需要转换的字段信息，则进行转换
                    if (excelHeadConvertMap != null && excelHeadConvertMap.get(fieldName) != null) {
                        valueObject = excelHeadConvertMap.get(fieldName).get(valueObject);
                    }
                    if (valueObject == null) {
                        megreCell(excelColumn, null, upCell, sheet, startRow, j, 1);
                        cell.setCellValue("");
                    } else if (valueObject instanceof Integer) {
                        megreCell(excelColumn, valueObject, upCell, sheet, startRow, j, 2);
                        cell.setCellValue((Integer) valueObject);
                    } else if (valueObject instanceof String) {
                        megreCell(excelColumn, valueObject, upCell, sheet, startRow, j, 3);
                        cell.setCellValue((String) valueObject);
                    } else if (valueObject instanceof Date) {
                        megreCell(excelColumn, valueObject, upCell, sheet, startRow, j, 4);
                        cell.setCellValue((Date) valueObject);
                        if (excelColumns.get(j).getDateFormat() != null) {
                            cell.setCellStyle(mapDateCellStyle.get(excelColumn.getIndex()));
                        } else {
                            cell.setCellStyle(defaultDateCellStyle);
                        }
                    } else if (valueObject instanceof Calendar) {
                        megreCell(excelColumn, valueObject, upCell, sheet, startRow, j, 5);
                        if (excelColumns.get(j).getDateFormat() != null) {
                            cell.setCellStyle(mapDateCellStyle.get(excelColumn.getIndex()));
                        } else {
                            cell.setCellStyle(defaultDateCellStyle);
                        }
                        cell.setCellValue((Calendar) valueObject);
                    } else if (valueObject instanceof Double) {
                        megreCell(excelColumn, valueObject, upCell, sheet, startRow, j, 6);
                        cell.setCellValue((Double) valueObject);
                    } else if (valueObject instanceof Boolean) {
                        megreCell(excelColumn, valueObject, upCell, sheet, startRow, j, 7);
                        cell.setCellValue((Boolean) valueObject);
                    } else {
                        megreCell(excelColumn, valueObject, upCell, sheet, startRow, j, 8);
                        cell.setCellValue(valueObject.toString());
                    }
                }
            }
        }
        for (int i = 0; i < excelColumns.size(); i++) {
            excelColumn = excelColumns.get(i);
            if (excelColumn.isMegre()) {
                sheet.addMergedRegion(new CellRangeAddress(excelColumn.getMegreStartRow(), startRow - 1, i, i));
                excelColumn.setFirst(true);
                excelColumn.setMegre(false);
            }
        }
    }

    /**
     * @param excelColumn 当前列的定义
     * @param valueObject 当前单元格的值
     * @param upCell      垂直上面的单元格
     * @param sheet
     * @param startRow    写到的第n行号
     * @param j           列数
     * @param type        单元格值的类型
     *                    void
     * @功能描述 合并单元格
     */
    private void megreCell(ExcelColumn excelColumn, Object valueObject, Cell upCell, Sheet sheet, int startRow, int j,
                           int type) {
        if (!excelColumn.isVerticalMerge() || upCell == null || startRow == 2) {
            return;
        }
        boolean flag = false;
        switch (type) {
            case 1:
                if (upCell.getStringCellValue().trim().equals("")) {
                    flag = true;
                }
                break;
            case 2:
                if ((Integer) valueObject == upCell.getNumericCellValue()) {
                    flag = true;
                }
                break;
            case 3:
                if (((String) valueObject).equals(upCell.getStringCellValue())) {
                    flag = true;
                }
                break;
            case 4:
                if (((Date) valueObject).compareTo(upCell.getDateCellValue()) == 0) {
                    flag = true;
                }
                break;
            case 5:
                if (((Calendar) valueObject).getTimeInMillis() == upCell.getDateCellValue().getTime()) {
                    flag = true;
                }
                break;
            case 6:
                if ((Double) valueObject == upCell.getNumericCellValue()) {
                    flag = true;
                }
                break;
            case 7:
                if ((Boolean) valueObject == upCell.getBooleanCellValue()) {
                    flag = true;
                }
                break;
            case 8:
                if (((String) valueObject).equals(upCell.getStringCellValue())) {
                    flag = true;
                }
                break;
            default:
                return;
        }
        if (flag) {
            if (excelColumn.isFirst()) {
                excelColumn.setMegreStartRow(startRow - 2);
                excelColumn.setFirst(false);
            }
            excelColumn.setMegre(true);
        } else {
            if (excelColumn.isMegre()) {
                sheet.addMergedRegion(new CellRangeAddress(excelColumn.getMegreStartRow(), startRow - 2, j, j));
                excelColumn.setFirst(true);
                excelColumn.setMegre(false);
            }
        }
    }

    /**
     * 初始化excel样式
     * initExcelStyle(这里用一句话描述这个方法的作用)
     *
     * @param wb
     * @param excelColumns
     * @return void
     * @throws
     */
    private void initExcelStyle(SXSSFWorkbook wb, List<ExcelColumn> excelColumns) {
        // 创建帮助类
        CreationHelper helper = wb.getCreationHelper();
        // 创建字体
        Font commonFont = createFonts(wb, (short) 200, false);
        Font headerFont = createFonts(wb, (short) 250, true);
        // 一般样式
        commonStyle = wb.createCellStyle();
        // 表头样式
        headerStyle = wb.createCellStyle();
        format = helper.createDataFormat();
        //默认日期格式
        defaultDateCellStyle = wb.createCellStyle();
        defaultDateCellStyle.setFont(commonFont);
        defaultDateCellStyle.setDataFormat(format.getFormat("yyyy-MM-dd"));
        commonStyle.setFont(commonFont);
        headerStyle.setFont(headerFont);
        //设置背景颜色
        setFillBackgroundColors(headerStyle, IndexedColors.GREEN.getIndex(), (short) CellStyle.SOLID_FOREGROUND);
        // 框线显示
        setCellBorderAndColorStyle(commonStyle);
        setCellBorderAndColorStyle(headerStyle);
        setCellBorderAndColorStyle(defaultDateCellStyle);
        // 居中显示
        setCellStyleAlignment(commonStyle);
        setCellStyleAlignment(headerStyle);
        setCellStyleAlignment(defaultDateCellStyle);
        //自定义日期格式
        mapDateCellStyle = new HashMap<Integer, CellStyle>();
        ExcelColumn excelColumn;
        for (int i = 0; i < excelColumns.size(); i++) {
            excelColumn = excelColumns.get(i);
            if (excelColumn.getDateFormat() != null) {
                CellStyle dateCellStyle = wb.createCellStyle();
                dateCellStyle.setFont(commonFont);
                dateCellStyle.setDataFormat(format.getFormat(excelColumn.getDateFormat()));
                setCellBorderAndColorStyle(dateCellStyle);// 框线显示
                setCellStyleAlignment(dateCellStyle);// 居中显示
                mapDateCellStyle.put(excelColumn.getIndex(), dateCellStyle);
            }
        }
    }

    /**
     * 设置背景色
     * setFillBackgroundColors(这里用一句话描述这个方法的作用)
     *
     * @param cellStyle
     * @param fg
     * @param fp
     * @return void
     * @throws
     */
    public void setFillBackgroundColors(CellStyle cellStyle, short fg, short fp) {
        cellStyle.setFillForegroundColor(fg);
        cellStyle.setFillPattern(fp);
    }

    /**
     * 框线显示
     *
     * @param cellStyle
     */
    private void setCellBorderAndColorStyle(CellStyle cellStyle) {
        // 设置一个单元格边框
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        // 设置一个单元格边框颜色
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
    }

    /**
     * 居中显示
     *
     * @param cellStyle
     */
    private void setCellStyleAlignment(CellStyle cellStyle) {
        // 设置左右
        //      cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        // 设置上下
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    }

    /**
     * 创建字体样式
     *
     * @param wb
     * @param fontSize 大小
     * @param isBlod   是否加粗
     * @return Font
     */
    private Font createFonts(Workbook wb, short fontSize, boolean isBlod) {
        // 创建Font对象
        Font font = wb.createFont();
        // 设置字体
        font.setFontName("微软雅黑");
        // 斜体
        font.setItalic(false);
        // 字体大小
        font.setFontHeight(fontSize);
        //是否加粗
        if (isBlod) {
            font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        }
        return font;
    }


    /**
     * @param mapDIYRep     Map<日期,Map<水表,DIYRepExport>>
     * @param mapMeterShort Map<区域, Map<水表code,MeterInfoShort>>
     * @param response
     */
    public void exportDIYReport(Map<String, Map<String, DIYRepExport>> mapDIYRep, Map<String,
            Map<String, MeterInfoShort>> mapMeterShort, HttpServletResponse response) {
        String fileName = new SimpleDateFormat("yyyyMMdd_HHmmss_SSS").format(new Date()) + ".xlsx";
        //
        try {
            response.setHeader("content-disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
        } catch (UnsupportedEncodingException e) {
            logger.error(e.getMessage(), e);
        }
        int maxRows = mapDIYRep.keySet().size() * mapMeterShort.keySet().size() * 3 + 2;
        SXSSFWorkbook wb = new SXSSFWorkbook(maxRows);
        //
        Sheet sheet = wb.createSheet("sheet1");
        //region 创建样式
        // 创建帮助类
        CreationHelper helper = wb.getCreationHelper();
        // 创建字体
        Font commonFont = createFonts(wb, (short) 200, false);
        Font headerFont = createFonts(wb, (short) 250, true);
        // 一般样式
        commonStyle = wb.createCellStyle();
        // 表头样式
        headerStyle = wb.createCellStyle();
        format = helper.createDataFormat();
        //默认日期格式
        defaultDateCellStyle = wb.createCellStyle();
        defaultDateCellStyle.setFont(commonFont);
        defaultDateCellStyle.setDataFormat(format.getFormat("yyyy-MM-dd"));
        commonStyle.setFont(commonFont);
        headerStyle.setFont(headerFont);
        //设置背景颜色
        setFillBackgroundColors(headerStyle, IndexedColors.GREEN.getIndex(), CellStyle.SOLID_FOREGROUND);
        // 框线显示
        setCellBorderAndColorStyle(commonStyle);
        setCellBorderAndColorStyle(headerStyle);
        setCellBorderAndColorStyle(defaultDateCellStyle);
        // 居中显示
        setCellStyleAlignment(commonStyle);
        setCellStyleAlignment(headerStyle);
        setCellStyleAlignment(defaultDateCellStyle);
        //endregion
        //写表头
        int rowNum = 0;
        Row rowHeader = null;
        rowHeader = sheet.createRow(rowNum);
        //
        Cell cell = rowHeader.createCell(0);
        cell.setCellValue("日期");
        sheet.setColumnWidth(0, defaultColumnWidth);
        //
        for (String strDate : mapDIYRep.keySet()) {
            rowNum++;
            Row row = sheet.createRow(rowNum);
            //写入日期
            cell = row.createCell(0);
            cell.setCellValue(strDate);
            //合并日期
            //sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum + mapMeterShort.keySet().size() * 3 - 1, 0, 0));
            //region 区域
            int areaIndex = 0;
            for (String areaId : mapMeterShort.keySet()) {
                int colIndex = 1;
                //region 添加区域名称
                if (areaIndex > 0) {
                    //不是第1个区域时，添加新行
                    rowNum++;
                    row = sheet.createRow(rowNum);
                }
                //
                areaIndex++;
                //
                cell = row.createCell(colIndex);
                cell.setCellStyle(headerStyle);
                Map<String, MeterInfoShort> temp = mapMeterShort.get(areaId);
                if (temp.keySet().size() > 0) {
                    //取出首个MeterInfoShort的AreaName
                    String meterCode = temp.keySet().toArray()[0].toString();
                    MeterInfoShort mis = temp.get(meterCode);
                    cell.setCellValue(mis.getAreaName());
                    //合并
                    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, colIndex, colIndex + temp.keySet().size() - 1));
                }
                //endregion
                //添加本区域的各个水表
                //region 扩展行表头名称行
                rowNum++;
                row = sheet.createRow(rowNum);
                int tempIndex = colIndex;
                for (String meterCode : temp.keySet()) {
                    MeterInfoShort mis = temp.get(meterCode);
                    cell = row.createCell(tempIndex);
                    cell.setCellStyle(commonStyle);
                    cell.setCellValue(mis.getAnotherName());
                    tempIndex++;
                }
                //endregion
                //region 扩展水表用量行
                rowNum++;
                row = sheet.createRow(rowNum);
                tempIndex = colIndex;
                Map<String, DIYRepExport> mapDayUsages = mapDIYRep.get(strDate);
                for (String meterCode : temp.keySet()) {
                    MeterInfoShort mis = temp.get(meterCode);
                    cell = row.createCell(tempIndex);
                    cell.setCellStyle(commonStyle);
                    DIYRepExport export = mapDayUsages.get(meterCode);
                    if (export != null) {
                        cell.setCellValue(export.getIntervalQuantity());
                    }
                    tempIndex++;
                }
                //endregion
            }
            //endregion

        }

        //region 导出到文件
        OutputStream out = null;
        try {
            out = response.getOutputStream();
            wb.write(out);
            out.flush();
        } catch (IOException e) {
            logger.error(e.getMessage(), e);
        }
        //
        try {
            if (out != null) {
                out.close();
            }
        } catch (IOException e) {
            logger.error(e.getMessage(), e);
        }
        //endregion
    }


    public void exportDIYReport(MeterMapper meterBiz,
                                List<Area> sortedAreas, List<String> listDate, Map<String, Map<String, DIYRepExport>> listData,
                                Map<String, Map<String, MeterInfoShort>> mapMeterInfo, HttpServletResponse response) {
        //region 文件名及reponse头
        String fileName = new SimpleDateFormat("yyyyMMdd_HHmmss_SSS").format(new Date());
        //
        try {
            response.setHeader("content-disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
        } catch (UnsupportedEncodingException e) {
            logger.error(e.getMessage(), e);
        }
        //endregion
        // 读取导出excel模板
        int maxRows = 10 + listDate.size() * mapMeterInfo.keySet().size() * 4;
        SXSSFWorkbook wb = new SXSSFWorkbook(maxRows);//SXSSFWorkbook用来处理大数据量以及超大数据量的导出，内存里一次只留 多少行
        Sheet sheet = wb.createSheet(fileName);
        int rowNum = 0;
        Row row = null;
        Row row2 = null;
        Row row3 = null;
        Cell cell = null;

        //
        initExcelStyle(wb, new LinkedList<ExcelColumn>());
        headerStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        headerStyle.setBorderBottom((short) 1);
        headerStyle.setBorderLeft((short) 1);
        headerStyle.setBorderRight((short) 1);
        headerStyle.setBorderTop((short) 1);

        sheet.setColumnWidth(0, 4000);
        for (int k = 1; k < 20; k++) {
            sheet.setColumnWidth(k, defaultColumnWidth);
        }

        //region 生成导出头
        row = sheet.createRow(rowNum);
        cell = row.createCell(0);
        cell.setCellValue("日期");
        //endregion

        try {
            //region 生成导出数据
            for (String strDate : listDate) {
                Map<String, DIYRepExport> meterData = listData.get(strDate);
                int i = 0;
                for (Area area : sortedAreas) {
                    Integer areaId = area.getAreaId();
                    List<MeterInfoShort> meters = meterBiz.queryMetersInfoByArea(areaId);
                    if (meters.isEmpty()) {
                        continue;
                    }
                    //region 第一行：日期、区域名称
                    rowNum++;
                    row = sheet.createRow(rowNum);

                    i++;
                    //写入日期
                    if (i == 1) {
                        //每天的第1行时写入日期，其他行不写
                        cell = row.createCell(0);
                        cell.setCellStyle(headerStyle);
                        cell.setCellValue(strDate);
                    }

                    //region 写入区域头
                    String areaName = "";

                    //String meterCode1 = areaMeter.keySet().toArray()[0].toString();
                    areaName = area.getAreaName(); //areaMeter.get(meterCode1).getAreaName();

                    cell = row.createCell(1);
                    cell.setCellStyle(headerStyle);
                    cell.setCellValue(areaName);
                    //
                    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 1, meters.size() + 1));
                    //endregion
                    //endregion

                    //region 第二、三行：各个水表表头名称&用水量
                    //第二行：各个水表名称
                    rowNum++;
                    row2 = sheet.createRow(rowNum);
                    //第三行：各个水表用量
                    rowNum++;
                    row3 = sheet.createRow(rowNum);
                    //
                    int j = 1;
                    for (MeterInfoShort meter : meters) {
                        String meterCode = meter.getDeviceName();
                        //表头名称
                        cell = row2.createCell(j);
                        cell.setCellStyle(commonStyle);
                        cell.setCellValue(meter.getAnotherName());
                        //表头数值
                        cell = row3.createCell(j);
                        cell.setCellStyle(commonStyle);
                        if (meterData != null && meterData.containsKey(meterCode)) {
                            DIYRepExport export = meterData.get(meterCode);
                            cell.setCellValue(export.getIntervalQuantity());
                        }
                        j++;
                    }
                    //尾部合计名称
                    cell = row2.createCell(j);
                    cell.setCellStyle(commonStyle);
                    cell.setCellValue("合计");
                    //尾部合计数值
                    cell = row3.createCell(j);
                    cell.setCellStyle(commonStyle);
                    if (meterData != null) {
                        DIYRepExport export = meterData.get(areaId);
                        if (export != null) {
                            cell.setCellValue(export.getIntervalQuantity());
                        }
                    }
                    //endregion
                }
            }
            //endregion
        } catch (Exception exc) {
            logger.error(exc.getMessage());
        }
        //region 导出到文件中
        OutputStream out = null;
        try {
            out = response.getOutputStream();
            wb.write(out);
            out.flush();
        } catch (IOException e) {
            logger.error(e.getMessage(), e);
        }
        try {
            if (out != null) {
                out.close();
            }
        } catch (IOException e) {
            logger.error(e.getMessage(), e);
        }
        //endregion
    }

}
